#!/bin/env python
#-*-coding:utf-8-*-

import pymysql
import string
import time  
import datetime
import sys,os
import configparser as ConfigParser
import smtplib
from email.mime.text import MIMEText
from email.message import Message
from email.header import Header
import logging
import logging.config

log_file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '../etc/logger.ini')
logging.config.fileConfig(log_file_path)
logger = logging.getLogger("lepus")

def get_item(data_dict, item):
    '''
    获取数据库的参数配置

    Args:
        data_dict: 数据库参数列表，show variables的结果集.
        item: 具体要获取的数据项目，例如version.

    Returns:
        返回具体的参数值，异常则返回-1
    '''
    try:
       item_value = data_dict[item]
       return item_value
    except:
       return '-1'

def get_config(group, config_name):
    '''
    读取配置文件，配置文件中是数据库的配置信息

        Args:
            group: 配置分组名称，默认是monitor_server
            config_name: 配置的名称，具体见config.ini的样例
        
        Returns:
            返回配置项目
    '''
    config = ConfigParser.ConfigParser()
    # 单元测试时临时调整成绝对路径
    config_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "../etc/config.ini")
    config.read(config_path)
    config_value=config.get(group, config_name).strip(' ').strip('\'').strip('\"')
    return config_value

def filters(data):
    return data.strip(' ').strip('\n').strip('\br')

host = get_config('monitor_server', 'host')
port = get_config('monitor_server', 'port')
user = get_config('monitor_server', 'user')
passwd = get_config('monitor_server', 'passwd')
dbname = get_config('monitor_server', 'dbname')

def mysql_exec(sql, param):
    '''
    执行execute方法的封装函数

    Args:
        sql: 要执行的sql，采用预编译形式，注意写占位符
        param: 参数，元组形式
        
    Raises:
        Exception: 数据库执行错误
    '''
    try:
        conn=pymysql.connect(host=host,
                             user=user,
                             passwd=passwd,
                             port=int(port),
                             connect_timeout=5,
                             charset='utf8')
        conn.select_db(dbname)
        curs = conn.cursor()
        try:
            if param != '':
                curs.execute(sql, param)
            else:
                curs.execute(sql)
            conn.commit()
        finally:
            curs.close()
            conn.close()
    except Exception as e:
       logger.error("mysql execute: " + str(e))

def mysql_query(sql):
    '''
    查询SQL的封装函数

    Args:
        sql: 要执行查询操作的sql语句
        
    Returns:
        返回执行结果集
    '''
    conn=pymysql.connect(host=host,
                         user=user,
                         passwd=passwd,
                         port=int(port),
                         connect_timeout=5,
                         charset='utf8')
    conn.select_db(dbname)
    cursor = conn.cursor()
    try:
        count=cursor.execute(sql)
        if count == 0 :
            result=0
        else:
            result=cursor.fetchall()
        return result
    finally:
        cursor.close()
        conn.close()

def add_alarm(server_id,
              tags,
              db_host,
              db_port,
              create_time,
              db_type,
              alarm_item,
              alarm_value,
              level,message,
              send_mail,
              send_mail_to_list):
    '''
    添加告警信息到数据库表中

    Args:
        server_id: 服务器编号，这是添加被监控服务器的时候指定的
        tags: 标签，配置添加监控服务器时指定
        db_host: 被监控节点的IP
        db_port: 被监控节点的数据库端口
        create_time: 记录生成时间，默认是当前时间
        db_type: mysql或者oracle这一类数据库类型
        alarm_item: 监控项，比如threads_connected，threads_running，threads_waits
        alarm_value: 监控项的值
        level: 告警级别（critical/warning/）
        message: 告警信息
        send_mail: 是否发送邮件，1-发送；0-不发送
        send_mail_to_list: 收件人列表，列表形式

        Raises:
            Exception: 数据库异常
    '''
    try: 
        conn=pymysql.connect(host=host,
                             user=user,
                             passwd=passwd,
                             port=int(port),
                             connect_timeout=5,
                             charset='utf8')
        conn.select_db(dbname)
        curs = conn.cursor()
        sql = '''
        insert into alarm(server_id,tags,host,port,create_time,db_type,
        alarm_item,alarm_value,level,message,send_mail,send_mail_to_list) 
        values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        '''
        param=(server_id,tags,db_host,db_port,create_time,db_type,alarm_item,alarm_value,level,message,send_mail,send_mail_to_list)
        curs.execute(sql,param)

        if send_mail == 1:
            temp_sql = '''
            insert into alarm_temp(server_id,ip,db_type,alarm_item,alarm_type) 
            values(%s,%s,%s,%s,%s)
            '''
            temp_param = (server_id, db_host, db_type, alarm_item, 'mail')
            curs.execute(temp_sql, temp_param)
        elif send_mail ==0:
            temp_sql = '''
            insert into alarm_temp(server_id,ip,db_type,alarm_item,alarm_type) 
            values(%s,%s,%s,%s,%s)
            '''
            temp_param = (server_id, db_host, db_type, alarm_item, 'none')
            curs.execute(temp_sql, temp_param)
        conn.commit()
        curs.close()
        conn.close()
    except Exception as e:
        logger.error("Add alarm: " + str(e))

def check_if_ok(server_id, 
                tags,
                db_host,
                db_port,
                create_time,
                db_type,
                alarm_item,
                alarm_value,
                message,
                send_mail,
                send_mail_to_list):
    '''
    检查告警状态，从数据库告警表中查询信息

    Args:
        server_id: 服务器编号，这是添加被监控服务器的时候指定的
        tags: 标签，配置添加监控服务器时指定
        db_host: 被监控节点的IP
        db_port: 被监控节点的数据库端口
        create_time: 记录生成时间，默认是当前时间
        db_type: mysql或者oracle这一类数据库类型
        alarm_item: 监控项，比如threads_connected，threads_running，threads_waits
        alarm_value: 监控项的值
        message: 告警信息
        send_mail: 是否发送邮件，1-发送；0-不发送
        send_mail_to_list: 收件人列表，列表形式

        Raises:
            Exception: 数据库异常
    '''
    conn = pymysql.connect(host=host,
                           user=user,
                           passwd=passwd,
                           port=int(port),
                           connect_timeout=5,
                           charset='utf8')
    conn.select_db(dbname)
    curs = conn.cursor()
    if db_type=='os':
        alarm_count = curs.execute("select id from alarm_temp where ip='{ip}' and alarm_item='{alarm_item}'".format(ip=db_host, alarm_item=alarm_item))
        curs.execute("delete from alarm_temp where ip='{db_host}'  and alarm_item='{alarm_item}'".format(db_host=db_host, alarm_item=alarm_item))
    else:
        alarm_count = curs.execute("select id from alarm_temp where ip='{ip}' and alarm_item='{alarm_item}' and db_type = '{db_type}'".format(ip=db_host, alarm_item=alarm_item, db_type=db_type))
        curs.execute("delete from alarm_temp where server_id={server_id} and db_type='{db_type}' and alarm_item='{alarm_item}'".format(db_type=db_type, server_id=server_id, alarm_item=alarm_item))
    if int(alarm_count) > 0 :
        sql="insert into alarm(server_id,tags,host,port,create_time,db_type,alarm_item,alarm_value,level,message,send_mail,send_mail_to_list) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        param=(server_id,tags,db_host,db_port,create_time,db_type,alarm_item,alarm_value,'ok',message,send_mail,send_mail_to_list)
        mysql_exec(sql, param)
    curs.close()
    conn.close()

def update_send_mail_status(server,
                            db_type,
                            alarm_item,
                            send_mail,
                            send_mail_max_count):
    '''
    更新发送邮件状态

    Args:
        server: 被监控服务器编号
        db_type: 数据库类型
        alarm_item: 告警项目
        send_mail: 是否发送邮件，其实我认为这里不应该作为入参，暂时保留
        send_mail_max_count: 最大发送次数
    Returns:
        返回是否发送的标记
    '''
    conn=pymysql.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=5,charset='utf8')
    conn.select_db(dbname)
    curs = conn.cursor()
    try:
        if db_type == "os":
            alarm_count=curs.execute("select id from alarm_temp where ip='%s' and db_type='%s' and alarm_item='%s' and alarm_type='mail' ;" %(server,db_type,alarm_item))
        else:
            alarm_count=curs.execute("select id from alarm_temp where server_id=%s and db_type='%s' and alarm_item='%s' and alarm_type='mail' ;" %(server,db_type,alarm_item)) 
        if int(alarm_count) >= int(send_mail_max_count) :
            send_mail = 0
        else:
            send_mail = send_mail
        return send_mail
    finally:
        curs.close()
        conn.close()

def check_db_status(server_id,
                    db_host,
                    db_port,
                    tags,
                    db_type):
    '''
    检查db_status表

    Args:
        server_id: 服务器Id
        db_host: 数据库地址
        db_port: 数据库端口
        tags: 标签
        db_type: 数据库类型
    
    Raises:
        Exception: 数据库异常
    '''
    try:
        conn=pymysql.connect(host=host,
                            user=user,
                            passwd=passwd,
                            port=int(port),
                            connect_timeout=5,
                            charset='utf8')
        conn.select_db(dbname)
        curs = conn.cursor()

        sql = "select id from db_status where host = '{}' and port = '{}'".format(db_host, db_port)
        count = curs.execute(sql) 
        if count == 0:
             if db_type == 'mysql':
                sort = 1
             elif db_type == 'oracle':
                sort = 2
             else:
                sort = 0
             sql="insert into db_status(server_id,host,port,tags,db_type,db_type_sort) values(%s,%s,%s,%s,%s,%s)"
             param=(server_id,db_host,db_port,tags,db_type,sort)
             curs.execute(sql,param)
             conn.commit()
    except Exception as e:
        logger.error("Check db status table: " + str(e))
    finally:
        curs.close()
        conn.close()

def update_db_status_init(role,
                          version,
                          db_host,
                          db_port,
                          tags):
    '''
    更新db_satus表，这里是初始化操作，配置的信息参考官方网址 http://www.dbarun.com/docs/lepus/instance/mysql/

    Args:
        role: 角色
        version: 版本
        db_host: 被监控节点的地址
        db_port: 数据库端口
        tags: 自定义标签
    '''
    try:
        conn = pymysql.connect(host=host,
                               user=user,
                               passwd=passwd,
                               port=int(port),
                               connect_timeout=5,
                               charset='utf8')
        conn.select_db(dbname)
        curs = conn.cursor()
        sql = "update db_status set role='{}',version='{}',tags='{}' where host='{}' and port='{}'".format(role, version, tags, db_host, db_port)
        curs.execute(sql)
        conn.commit()
    except Exception as e:
        logger.error("update db status init: " + str(e))
    finally:
      curs.close()
      conn.close()

def update_db_status(field,
                     value,
                     db_host,
                     db_port,
                     alarm_time,
                     alarm_item,
                     alarm_value,
                     alarm_level):
    '''
    更新数据库状态表db_status

    Args:
        field: 表中的字段名
        value: 要更新字段的值
        db_host: 数据库地址
        db_port: 端口
        alarm_time: 告警时间
        alarm_item: 告警项目
        alarm_value: 告警内容
        alarm_level: 告警级别
    '''
    try:
        field_tips=field + '_tips'
        if value == -1:
            value_tips = 'no data'
        else:
            value_tips = """
                          item: {}\n<br/>
                          value: {}\n<br/>
                          level: {}\n<br/>
                          time: {}\n<br/>
                    """.format(alarm_item, alarm_value, alarm_level, alarm_time)

        conn = pymysql.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=5,charset='utf8')
        conn.select_db(dbname)
        curs = conn.cursor()
        if db_port:
            sql = "update db_status set {}='{}',{}='{}' where host='{}' and port='{}'".format(field,value,field_tips,value_tips,db_host,db_port)
        else:
            sql = "update db_status set {}='{}',{}='{}' where host='{}'".format(field,value,field_tips,value_tips,db_host)
        curs.execute(sql)
        conn.commit()
    except Exception as e:
        logger.error("update db status: " + str(e))
    finally:
        curs.close()
        conn.close()

def update_check_time():
    '''
    更新lepus_status表中lepus_checktime参数对应的值

    Raises:
        数据库异常
    '''
    try:
        conn = pymysql.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=5,charset='utf8')
        conn.select_db(dbname)
        curs = conn.cursor()
        localtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
        sql = "update lepus_status set lepus_value='{}' where lepus_variables='lepus_checktime'".format(localtime)
        curs.execute(sql)
        conn.commit()
    except Exception as e:
        logger.error("update check time: " + str(e))
    finally:
        curs.close()
        conn.close()

def get_option(key):
    '''
    查询options表，该表中是一些元信息

    Args:
        要查询的配置项

    Returns:
       返回要查询的配置项的值
    '''
    conn=pymysql.connect(host=host,
                         user=user,
                         passwd=passwd,
                         port=int(port),
                         connect_timeout=5,
                         charset='utf8')
    conn.select_db(dbname)
    cursor = conn.cursor()
    sql="select value from options where name = '{}'".format(key)
    try:
        count = cursor.execute(sql)
        if count == 0:
            result = 0
        else:
            result = cursor.fetchone()
        return result[0]
    finally:
        cursor.close()
        conn.close()

def flush_hosts():
    '''
    清理数据库缓存
    '''
    conn=pymysql.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=5,charset='utf8')
    conn.select_db(dbname)
    cursor = conn.cursor()
    try:
        cursor.execute('flush hosts')
    finally:
        cursor.close()
        conn.close()

def get_mysql_status(conn):
    '''
    得到show global status的结果

    Args:
        cursor: 数据库游标
    
    Returns: 
        状态和状态值组成的字典列表
    '''
    cursor = conn.cursor()
    cursor.execute('show global status')
    try:
        data_list=cursor.fetchall()
    finally:
        cursor.close()
    data_dict={}
    for item in data_list:
        data_dict[item[0]] = item[1]
    return data_dict

def get_mysql_variables(conn):
    '''
    获得show global variables的结果

    Args:
        cursor: 数据库游标
    
    Returns: 
        状态和状态值组成的字典列表
    '''
    cursor = conn.cursor()
    cursor.execute('show global variables')
    try:
        data_list=cursor.fetchall()
    finally:
        cursor.close()
    data_dict={}
    for item in data_list:
        data_dict[item[0]] = item[1]
    return data_dict

def get_mysql_version(cursor):
    '''
    获得数据库版本号
    Args:
        cursor: 数据库游标
    
    Returns: 
        状态和状态值组成的字典列表
    '''
    try:
        cursor.execute('select version()')
        result = cursor.fetchone()[0]
    finally:
        cursor.close()
    return result

mail_host = get_option('smtp_host')
mail_port = int(get_option('smtp_port'))
mail_user = get_option('smtp_user')
mail_pass = get_option('smtp_pass')
mail_send_from = get_option('mailfrom')

def send_mail(to_list, sub, content):
    '''
    发送邮件函数
    
    Args:
        to_list: 收件人列表
        sub: 邮件主题
        content: 邮件内容
    
    Returns: 
        发送成功返回True，失败返回False
    '''
    from_addr = mail_send_from
    msg = MIMEText(content, _subtype='html', _charset='utf8')
    msg['Subject'] = Header(sub,'utf8')
    msg['From'] = Header(from_addr,'utf8')
    msg['To'] = ";".join(to_list)
    try:
        smtp = smtplib.SMTP()
        smtp.connect(mail_host, mail_port)
        smtp.login(mail_user, mail_pass)
        smtp.sendmail(from_addr, to_list, msg.as_string())
        smtp.close()
        return True
    except Exception as e:
        logger.error(str(e))
        return False
